﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuanLyKhachHang.Forms
{
    #region using
    using QuanLyKhachHang.BUS;
    using QuanLyKhachHang.DTO;
    using QuanLyKhachHang.Utilities;
    using System.IO;
    using NPOI.HSSF.UserModel;
    #endregion

    public partial class frmImportKhachHangLon : Form
    {
        private KH_LONBUS objBUS;
        private DataTable dataTable = new DataTable();
        private string maDonVi;
        private int thang;
        private int nam;
        private List<KhachHangDTO> lstKhachHangNotMatching = new List<KhachHangDTO>();
        private delegate void TimeTask();
        private frmWaiting waiting;
        private DataSet objKhachHangDS;
        private DataSet objDonViDS;
        private string sheetname;

        public frmImportKhachHangLon()
        {
            InitializeComponent();
            InitFormSoSanh();
        }

        private void WaitLoad()
        {
            waiting = new frmWaiting("Đang lấy dữ liệu");
            waiting.ShowDialog();
        }

        private void frmImportKhachHangLon_Load(object sender, EventArgs e)
        {
            LoadDonVi();
        }

        public void LoadDonVi()
        {
            var objBUS = new DonViBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objDonViDS = objBUS.SelectAll();

                for (int i = 0; i < objDonViDS.Tables["DonVi"].Rows.Count; i++)
                {
                    DataRow row = objDonViDS.Tables["DonVi"].Rows[i];
                    cbxDonVi.Items.Add(new DonViDTO { MADONVI = row["MADONVI"].ToString(), TENDONVI = row["TENDONVI"].ToString() });
                }

                if (cbxDonVi.Items.Count > 0)
                    cbxDonVi.SelectedIndex = 0;
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void LoadKhachHang(string madonvi, int thang, int nam)
        {
            var objBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);

            try
            {
                //select kh by madonvi, thang, nam
                objKhachHangDS = objBUS.SelectByMADONVI(madonvi, thang, nam);
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void LoadAllOfKhachHang()
        {
            var objBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);

            try
            {
                //select kh by madonvi, thang, nam
                objKhachHangDS = objBUS.SelectAll();
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public bool CheckingMatching(KhachHangDTO khmoi, KhachHangDTO khcu)
        {
            bool IsSame = false;
            if (khcu.MADONVI.ToLower() == khmoi.MADONVI.ToLower())
            {
                string tenkhbodaucu = Utils.RemoveSign4VietnameseString(khcu.TENKH).Trim().ToLower();
                string tenkhbodaumoi = Utils.RemoveSign4VietnameseString(khmoi.TENKH).Trim().ToLower();

                Utils.ComparePrefix(ref tenkhbodaucu, ref tenkhbodaumoi);

                string[] compkhcu = tenkhbodaucu.Split(new char[] { ' ' });
                string[] compkhmoi = tenkhbodaumoi.Split(new char[] { ' ' });

                if (compkhcu.Length == compkhmoi.Length)
                {
                    for (int j = 0; j < compkhcu.Length; j++)
                    {
                        if (compkhcu[j] == compkhmoi[j])
                            IsSame = true;
                        else
                        {
                            IsSame = false;
                            break;
                        }
                    }
                }
            }
            return IsSame;
        }
        public bool CheckingTheSameAs(KhachHangDTO khmoi, ref KhachHangDTO returnedKHCu)
        {
            bool IsSame = false;
            for (int i = 0; i < objKhachHangDS.Tables[0].Rows.Count; i++)
            {
                DataRow row = objKhachHangDS.Tables[0].Rows[i];
                KhachHangDTO khcu = new KhachHangDTO
                {
                    MAKH = int.Parse(row["MAKH"].ToString()),
                    TENKH = row["TENKH"].ToString(),
                    DIACHI = row["DIACHI"].ToString(),
                    MADONVI = row["MADONVI"].ToString()
                };

                IsSame = CheckingMatching(khmoi, khcu);

                if (IsSame)
                {
                    returnedKHCu = khcu;
                    break;
                }
            }

            return IsSame;
        }

        public void InitFormSoSanh()
        {
            string str = "";
            long i = 0;
            DataColumn colTenKHCu = new DataColumn("TENKHCU", str.GetType());
            DataColumn colDiaChiCu = new DataColumn("DIACHICU", str.GetType());
            DataColumn colDonViCu = new DataColumn("DONVICU", str.GetType());

            DataColumn colTenKHMoi = new DataColumn("TENKHMOI", str.GetType());
            DataColumn colDiaChiMoi = new DataColumn("DIACHIMOI", str.GetType());
            DataColumn colDonViMoi = new DataColumn("DONVIMOI", str.GetType());

            DataColumn colTongTrichThuong = new DataColumn("TONGTRICHTHUONG", i.GetType());
            DataColumn colTongChietKhau = new DataColumn("TONGCHIETKHAU", i.GetType());
            DataColumn colChiTrichThuong = new DataColumn("CHITRICHTHUONG", i.GetType());
            DataColumn colChiChietKhau = new DataColumn("CHICHIETKHAU", i.GetType());

            //Dich vu
            DataColumn colBPTN = new DataColumn("BPTN", i.GetType());
            DataColumn colBKTN = new DataColumn("BKTN", i.GetType());
            DataColumn colEMSTN = new DataColumn("EMSTN", i.GetType());
            DataColumn colEMSNN = new DataColumn("EMSNN", i.GetType());
            DataColumn colVE = new DataColumn("VE", i.GetType());
            DataColumn colBCUT = new DataColumn("BCUT", i.GetType());
            DataColumn colTG = new DataColumn("TG", i.GetType());
            DataColumn colTM = new DataColumn("TM", i.GetType());
            DataColumn colTBT = new DataColumn("TBT", i.GetType());

            dataTable.Columns.Add(colTenKHCu);
            dataTable.Columns.Add(colDiaChiCu);
            dataTable.Columns.Add(colDonViCu);
            dataTable.Columns.Add(colTenKHMoi);
            dataTable.Columns.Add(colDiaChiMoi);
            dataTable.Columns.Add(colDonViMoi);

            dataTable.Columns.Add(colTongTrichThuong);
            dataTable.Columns.Add(colTongChietKhau);
            dataTable.Columns.Add(colChiTrichThuong);
            dataTable.Columns.Add(colChiChietKhau);

            //dich vu
            dataTable.Columns.Add(colBPTN);
            dataTable.Columns.Add(colBKTN);
            dataTable.Columns.Add(colEMSTN);
            dataTable.Columns.Add(colEMSNN);
            dataTable.Columns.Add(colVE);
            dataTable.Columns.Add(colBCUT);
            dataTable.Columns.Add(colTG);
            dataTable.Columns.Add(colTM);
            dataTable.Columns.Add(colTBT);
        }

        public bool IsFloatNumeric(string number)
        {
            try
            {
                long num = long.Parse(number);
                return false;
            }
            catch
            {
                return true;
            }
        }

        public void AddKhachHang_TTCK(DataRow row)
        {
            try
            {
                KhachHangDTO objDto = new KhachHangDTO
                {
                    MAKH = int.Parse(row["MAKH"].ToString()),
                    MADONVI = maDonVi,
                    TENKH = row["TENKH"].ToString(),
                    DIACHI = row["DIACHI"].ToString()
                };

                TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                {
                    MAKH = objDto.MAKH,
                    THANG = thang,
                    NAM = nam,
                    TONGTRICHTHUONG = long.Parse(row["TONGTRICHTHUONG"].ToString()),
                    TONGCHIETKHAU = long.Parse(row["TONGCHIETKHAU"].ToString()),
                    CHITRICHTHUONG = long.Parse(row["CHITRICHTHUONG"].ToString()),
                    CHICHIETKHAU = long.Parse(row["CHICHIETKHAU"].ToString())
                };

                var objTTCKBUS = new TrichThuong_ChietKhauBUS(GlobalVariables.g_strConnectionString);
                if (objTTCKBUS.Delete(objDto.MAKH, thang, nam))
                {
                    objTTCKBUS = new TrichThuong_ChietKhauBUS(GlobalVariables.g_strConnectionString);
                    if (objTTCKBUS.Insert(objttckDto))
                    {
                        ContinueAddTongHopChiTiet_NotExist(row, objDto.MAKH);
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        public void AddKhachHangAndConcerns_NotExist(DataRow row)
        {
            try
            {
                var objBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);

                KhachHangDTO objDto = new KhachHangDTO
                {
                    MADONVI = maDonVi,
                    TENKH = row["TENKH"].ToString(),
                    DIACHI = row["DIACHI"].ToString()
                };

                TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                {
                    THANG = thang,
                    NAM = nam,
                    TONGTRICHTHUONG = long.Parse(row["TONGTRICHTHUONG"].ToString()),
                    TONGCHIETKHAU = long.Parse(row["TONGCHIETKHAU"].ToString()),
                    CHITRICHTHUONG = long.Parse(row["CHITRICHTHUONG"].ToString()),
                    CHICHIETKHAU = long.Parse(row["CHICHIETKHAU"].ToString())
                };
                if (objBUS.InsertAndConcerns(objDto, objttckDto))
                {
                    ContinueAddTongHopChiTiet_NotExist(row, objDto.MAKH);
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        public void UpdateKhachHangAndConcerns_Exist(DataRow row)
        {
            try
            {
                var objBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);

                KhachHangDTO objDto = new KhachHangDTO
                {
                    MADONVI = maDonVi,
                    TENKH = row["TENKH"].ToString(),
                    DIACHI = row["DIACHI"].ToString()
                };

                TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                {
                    THANG = thang,
                    NAM = nam,
                    TONGTRICHTHUONG = long.Parse(row["TONGTRICHTHUONG"].ToString()),
                    TONGCHIETKHAU = long.Parse(row["TONGCHIETKHAU"].ToString()),
                    CHITRICHTHUONG = long.Parse(row["CHITRICHTHUONG"].ToString()),
                    CHICHIETKHAU = long.Parse(row["CHICHIETKHAU"].ToString())
                };
                if (objBUS.UpdateAndConcerns(objDto, objttckDto))
                {
                    ContinueAddTongHopChiTiet_NotExist(row, objDto.MAKH);
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        public void ContinueAddTongHopChiTiet_NotExist(DataRow row, int makh)
        {
            //tiep tuc thu hien them thong tin vao bang tong hop va chi tiet
            //kiem tra khac hang o thang x nam y da ton tai trong bang tong hop hay chua
            //neu chua, thuc hien them record moi
            //da ton tai, remove record co san va them record moi
            IDictionary<string, string> tonghop_dichvu = new Dictionary<string, string>();

            tonghop_dichvu.Add("BPTN", row["BPTN"].ToString());
            tonghop_dichvu.Add("BKTN", row["BKTN"].ToString());
            tonghop_dichvu.Add("EMSTN", row["EMSTN"].ToString());
            tonghop_dichvu.Add("EMSNN", row["EMSNN"].ToString());
            tonghop_dichvu.Add("VE", row["VE"].ToString());
            tonghop_dichvu.Add("BCUT", row["BCUT"].ToString());
            tonghop_dichvu.Add("TG", row["TG"].ToString());
            tonghop_dichvu.Add("TM", row["TM"].ToString());
            tonghop_dichvu.Add("TBT", row["TBT"].ToString());

            if (tonghop_dichvu.Count > 0)
            {
                var tonghop = Utils.CheckingItemIsExist(objKhachHangDS.Tables[0], makh, thang, nam);
                if (tonghop == null)
                {
                    TongHopDTO objTHDto = new TongHopDTO
                    {
                        MAKH = makh,
                        THANG = thang,
                        NAM = nam
                    };

                    //insert new record vao bang tong hop
                    var objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                    if (objTHBUS.Insert(objTHDto))
                    {
                        //if exist tonghop, just remove
                        var objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                        objCTBUS.DeleteByMATONGHOP(objTHDto.MATONGHOP);
                        for (int i = 0; i < tonghop_dichvu.Count; i++)
                        {
                            var ctDto = new ChiTietDTO
                            {
                                MADICHVU = tonghop_dichvu.Keys.ToList()[i],
                                MATONGHOP = objTHDto.MATONGHOP,
                                SOTIEN = long.Parse(tonghop_dichvu.Values.ToList()[i])
                            };
                            objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                            objCTBUS.Insert(ctDto);
                        }
                    }
                }
                else
                {
                    //if exist tonghop, just remove
                    var objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                    objCTBUS.DeleteByMATONGHOP(int.Parse(tonghop["MATONGHOP"].ToString()));
                    var objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                    objTHBUS.Delete(int.Parse(tonghop["MATONGHOP"].ToString()));

                    TongHopDTO objTHDto = new TongHopDTO
                    {
                        MAKH = makh,
                        THANG = thang,
                        NAM = nam
                    };

                    //insert new record vao bang tong hop
                    objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                    if (objTHBUS.Insert(objTHDto))
                    {
                        for (int i = 0; i < tonghop_dichvu.Count; i++)
                        {
                            var ctDto = new ChiTietDTO
                            {
                                MADICHVU = tonghop_dichvu.Keys.ToList()[i],
                                MATONGHOP = objTHDto.MATONGHOP,
                                SOTIEN = long.Parse(tonghop_dichvu.Values.ToList()[i])
                            };
                            objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                            objCTBUS.Insert(ctDto);
                        }
                    }
                }
            }
        }

        public bool ImportKhachHangLon(string path, ref string errorText)
        {
            errorText = "";
            try
            {
                dataTable.Clear();
                dtgvDanhSachHienThi.DataSource = null;
                dtgvDanhSachHienThi.Columns.Clear();

                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    var dt = new DataTable("KhachHang");
                    dt.Columns.Add("TENKH");
                    dt.Columns.Add("DIACHI");
                    dt.Columns.Add("TONGTRICHTHUONG");
                    dt.Columns.Add("TONGCHIETKHAU");
                    dt.Columns.Add("CHITRICHTHUONG");
                    dt.Columns.Add("CHICHIETKHAU");

                    //dich vu
                    dt.Columns.Add("BPTN");
                    dt.Columns.Add("BKTN");
                    dt.Columns.Add("EMSTN");
                    dt.Columns.Add("EMSNN");
                    dt.Columns.Add("VE");
                    dt.Columns.Add("BCUT");
                    dt.Columns.Add("TG");
                    dt.Columns.Add("TM");
                    dt.Columns.Add("TBT");
                    dt.Columns.Add("MAKH");
                    int indexRow = 0;
                    while (rows.MoveNext())
                    {
                        HSSFRow row = (HSSFRow)rows.Current;
                        switch (indexRow)
                        {
                            case 1:
                                //get donvi infos
                                string[] compdonvi = row.Cells[0].ToString().Trim().Split(new char[] { ':' });
                                var tendonvi = compdonvi[1].Trim();
                                if (tendonvi.Trim().ToUpper() != ((DonViDTO)cbxDonVi.SelectedItem).TENDONVI.Trim().ToUpper())
                                {
                                    errorText = "Tên đơn vị được chọn và tên đơn vị trong file import không giống nhau, vui lòng xem lại file import";
                                    return false;
                                }
                                break;

                            case 3:
                            case 4:
                                if (row.Cells[0].ToString() != "")
                                {
                                    if (row.Cells[0].StringCellValue.Trim().ToLower().Contains("tháng") &&
                                        row.Cells[0].StringCellValue.Trim().ToLower().Contains("năm"))
                                    {
                                        //get month, year infos
                                        if (!Utils.ValidateNgayThang(row.Cells[0].ToString(), ref thang, ref nam, ref errorText))
                                            return false;
                                        lblThang.Text = thang.ToString();
                                        lblNam.Text = nam.ToString();
                                    }
                                }
                                break;

                            default:
                                if (Utils.IsNumeric(row.Cells[0].ToString()))
                                {
                                    dt.Rows.Add(row.Cells[1].StringCellValue, row.Cells[2].StringCellValue,
                                                row.Cells[12].NumericCellValue, row.Cells[14].NumericCellValue,
                                                row.Cells[13].NumericCellValue, row.Cells[15].NumericCellValue,
                                                row.Cells[3].NumericCellValue,
                                                row.Cells[4].NumericCellValue,
                                                row.Cells[5].NumericCellValue,
                                                row.Cells[6].NumericCellValue,
                                                row.Cells[7].NumericCellValue,
                                                row.Cells[8].NumericCellValue,
                                                row.Cells[9].NumericCellValue,
                                                row.Cells[10].NumericCellValue,
                                                row.Cells[11].NumericCellValue);
                                }
                                break;
                        }
                        indexRow++;
                    }

                    //load danh sach khach hang da co san trong DB
                    //LoadKhachHang(maDonVi, thang, nam);
                    LoadAllOfKhachHang();

                    int count = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow row = dt.Rows[i];
                        if (IsFloatNumeric(row["TONGCHIETKHAU"].ToString()))
                        {
                            double num = double.Parse(row["TONGCHIETKHAU"].ToString());
                            num = Math.Round(num);
                            row["TONGCHIETKHAU"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["TONGCHIETKHAU"] = long.Parse(row["TONGCHIETKHAU"].ToString() == "" ? "0" : row["TONGCHIETKHAU"].ToString());
                        }

                        if (IsFloatNumeric(row["CHICHIETKHAU"].ToString()))
                        {
                            double num = double.Parse(row["CHICHIETKHAU"].ToString());
                            num = Math.Round(num);
                            row["CHICHIETKHAU"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["CHICHIETKHAU"] = long.Parse(row["CHICHIETKHAU"].ToString() == "" ? "0" : row["CHICHIETKHAU"].ToString());
                        }

                        if (IsFloatNumeric(row["TONGTRICHTHUONG"].ToString()))
                        {
                            double num = double.Parse(row["TONGTRICHTHUONG"].ToString());
                            num = Math.Round(num);
                            row["TONGTRICHTHUONG"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["TONGTRICHTHUONG"] = long.Parse(row["TONGTRICHTHUONG"].ToString() == "" ? "0" : row["TONGTRICHTHUONG"].ToString());
                        }

                        if (IsFloatNumeric(row["CHITRICHTHUONG"].ToString()))
                        {
                            double num = double.Parse(row["CHITRICHTHUONG"].ToString());
                            num = Math.Round(num);
                            row["CHITRICHTHUONG"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["CHITRICHTHUONG"] = long.Parse(row["CHITRICHTHUONG"].ToString() == "" ? "0" : row["CHITRICHTHUONG"].ToString());
                        }
                        KhachHangDTO khmoi = new KhachHangDTO
                        {
                            TENKH = row["TENKH"].ToString(),
                            DIACHI = row["DIACHI"].ToString(),
                            MADONVI = maDonVi
                        };
                        //khong match thi them vao bang khach hang
                        KhachHangDTO khcu = new KhachHangDTO();

                        if (CheckingTheSameAs(khmoi, ref khcu))
                        {
                            //neu user da ton tai trong db
                            //update thong tin ve trich thuong, chiet khau ung voi user do
                            row["MAKH"] = khcu.MAKH;
                            AddKhachHang_TTCK(row);
                            //update thong tin ve tong hop
                            //ContinueAddTongHopChiTiet_NotExist(row, khcu.MAKH);

                            DataRow row2;
                            dataTable.Rows.Add();
                            row2 = dataTable.Rows[count];

                            row2["TENKHMOI"] = khmoi.TENKH;
                            row2["DIACHIMOI"] = khmoi.DIACHI;
                            row2["DONVIMOI"] = khmoi.MADONVI;

                            row2["TENKHCU"] = khcu.TENKH;
                            row2["DIACHICU"] = khcu.DIACHI;
                            row2["DONVICU"] = khcu.MADONVI;

                            row2["TONGTRICHTHUONG"] = long.Parse(row["TONGTRICHTHUONG"].ToString() == "" ? "0" : row["TONGTRICHTHUONG"].ToString());
                            row2["TONGCHIETKHAU"] = long.Parse(row["TONGCHIETKHAU"].ToString());
                            row2["CHITRICHTHUONG"] = long.Parse(row["CHITRICHTHUONG"].ToString() == "" ? "0" : row["CHITRICHTHUONG"].ToString());
                            row2["CHICHIETKHAU"] = long.Parse(row["CHICHIETKHAU"].ToString());

                            row2["BPTN"] = long.Parse(row["BPTN"].ToString() == "" ? "0" : row["BPTN"].ToString());
                            row2["BKTN"] = long.Parse(row["BKTN"].ToString() == "" ? "0" : row["BKTN"].ToString());
                            row2["EMSTN"] = long.Parse(row["EMSTN"].ToString() == "" ? "0" : row["EMSTN"].ToString());
                            row2["EMSNN"] = long.Parse(row["EMSNN"].ToString() == "" ? "0" : row["EMSNN"].ToString());
                            row2["VE"] = long.Parse(row["VE"].ToString() == "" ? "0" : row["VE"].ToString());
                            row2["BCUT"] = long.Parse(row["BCUT"].ToString() == "" ? "0" : row["BCUT"].ToString());
                            row2["TG"] = long.Parse(row["TG"].ToString() == "" ? "0" : row["TG"].ToString());
                            row2["TM"] = long.Parse(row["TM"].ToString() == "" ? "0" : row["TM"].ToString());
                            row2["TBT"] = long.Parse(row["TBT"].ToString() == "" ? "0" : row["TBT"].ToString());

                            count++;
                        }
                        else
                        {
                            AddKhachHangAndConcerns_NotExist(dt.Rows[i]);
                        }
                    }

                    DataView objTimeSheetDV = new System.Data.DataView(dataTable);
                    objTimeSheetDV.AllowNew = false;
                    dtgvDanhSachHienThi.AutoGenerateColumns = false;
                    dtgvDanhSachHienThi.DataSource = objTimeSheetDV;

                    DataGridViewTextBoxColumn objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH mới",
                        DataPropertyName = "TenKHMoi",
                        Name = "TenKHMoi",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Đơn vị mới",
                        DataPropertyName = "DonViMoi",
                        Name = "DonViMoi",
                        Width = 60
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);


                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ mới",
                        DataPropertyName = "DiaChiMoi",
                        Name = "DiaChiMoi",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH cũ",
                        DataPropertyName = "TenKHCu",
                        Name = "TenKHCu",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Đơn vị cũ",
                        DataPropertyName = "DonViCu",
                        Name = "DonViCu",
                        Width = 60
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ cũ",
                        DataPropertyName = "DiaChiCu",
                        Name = "DiaChiCu",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TongTrichThuong",
                        Name = "TongTrichThuong",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TongChietKhau",
                        Name = "TongChietKhau",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "ChiTrichThuong",
                        Name = "ChiTrichThuong",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "ChiChietKhau",
                        Name = "ChiChietKhau",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    //////////////////////////////////////////////////
                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BPTN",
                        Name = "BPTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BKTN",
                        Name = "BKTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSTN",
                        Name = "EMSTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSNN",
                        Name = "EMSNN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "VE",
                        Name = "VE",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BCUT",
                        Name = "BCUT",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TG",
                        Name = "TG",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TM",
                        Name = "TM",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TBT",
                        Name = "TBT",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);
                    //////////////////////////////////////////////////
                    DataGridViewButtonColumn objColumnButton = new DataGridViewButtonColumn
                    {
                        HeaderText = "Thêm",
                        Text = "Thêm",
                        DataPropertyName = "Them",
                        Width = 80,
                        Name = "btnThem",
                        UseColumnTextForButtonValue = true,
                        ToolTipText = "Thêm thông tin vào bảng khách hàng và các bảng liên quan"
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumnButton);

                    objColumnButton = new DataGridViewButtonColumn
                    {
                        HeaderText = "Cập nhật",
                        Text = "Cập nhật",
                        DataPropertyName = "CapNhat",
                        Width = 80,
                        Name = "btnCapNhat",
                        UseColumnTextForButtonValue = true,
                        ToolTipText = "Cập nhật thông tin của khách hàng và các thông tin liên quan"
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumnButton);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool ReImportKhachHangLon(string path, ref string errorText)
        {
            errorText = "";
            try
            {
                dataTable.Clear();
                dtgvDanhSachHienThi.DataSource = null;
                dtgvDanhSachHienThi.Columns.Clear();

                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    var dt = new DataTable("KhachHang");
                    dt.Columns.Add("TENKH");
                    dt.Columns.Add("DIACHI");
                    dt.Columns.Add("TONGTRICHTHUONG");
                    dt.Columns.Add("TONGCHIETKHAU");
                    dt.Columns.Add("CHITRICHTHUONG");
                    dt.Columns.Add("CHICHIETKHAU");

                    //dich vu
                    dt.Columns.Add("BPTN");
                    dt.Columns.Add("BKTN");
                    dt.Columns.Add("EMSTN");
                    dt.Columns.Add("EMSNN");
                    dt.Columns.Add("VE");
                    dt.Columns.Add("BCUT");
                    dt.Columns.Add("TG");
                    dt.Columns.Add("TM");
                    dt.Columns.Add("TBT");

                    int indexRow = 0;
                    while (rows.MoveNext())
                    {
                        HSSFRow row = (HSSFRow)rows.Current;
                        switch (indexRow)
                        {
                            case 1:
                                string[] compdonvi = row.Cells[0].ToString().Trim().Split(new char[] { ':' });
                                var tendonvi = compdonvi[1].Trim();
                                if (tendonvi.Trim().ToUpper() != ((DonViDTO)cbxDonVi.SelectedItem).TENDONVI.Trim().ToUpper())
                                {
                                    errorText = "Tên đơn vị được chọn và tên đơn vị trong file import không giống nhau, vui lòng xem lại file import";
                                    return false;
                                }
                                break;

                            case 3:
                            case 4:
                                if (row.Cells[0].ToString() != "")
                                {
                                    if (row.Cells[0].StringCellValue.Trim().ToLower().Contains("tháng") &&
                                        row.Cells[0].StringCellValue.Trim().ToLower().Contains("năm"))
                                    {
                                        //get month, year infos
                                        if (!Utils.ValidateNgayThang(row.Cells[0].ToString(), ref thang, ref nam, ref errorText))
                                            return false;
                                        lblThang.Text = thang.ToString();
                                        lblNam.Text = nam.ToString();
                                    }
                                }
                                break;

                            default:
                                if (Utils.IsNumeric(row.Cells[0].ToString()))
                                {
                                    dt.Rows.Add(row.Cells[1].StringCellValue, row.Cells[2].StringCellValue,
                                                row.Cells[12].NumericCellValue, row.Cells[14].NumericCellValue,
                                                row.Cells[13].NumericCellValue, row.Cells[15].NumericCellValue,
                                                row.Cells[3].NumericCellValue,
                                                row.Cells[4].NumericCellValue,
                                                row.Cells[5].NumericCellValue,
                                                row.Cells[6].NumericCellValue,
                                                row.Cells[7].NumericCellValue,
                                                row.Cells[8].NumericCellValue,
                                                row.Cells[9].NumericCellValue,
                                                row.Cells[10].NumericCellValue,
                                                row.Cells[11].NumericCellValue);
                                }
                                break;
                        }
                        indexRow++;
                    }

                    //load danh sach khach hang da co san trong DB
                    //LoadKhachHang(maDonVi, thang, nam);
                    LoadAllOfKhachHang();

                    int count = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow row = dt.Rows[i];
                        if (IsFloatNumeric(row["TONGCHIETKHAU"].ToString()))
                        {
                            double num = double.Parse(row["TONGCHIETKHAU"].ToString());
                            num = Math.Round(num);
                            row["TONGCHIETKHAU"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["TONGCHIETKHAU"] = long.Parse(row["TONGCHIETKHAU"].ToString() == "" ? "0" : row["TONGCHIETKHAU"].ToString());
                        }

                        if (IsFloatNumeric(row["CHICHIETKHAU"].ToString()))
                        {
                            double num = double.Parse(row["CHICHIETKHAU"].ToString());
                            num = Math.Round(num);
                            row["CHICHIETKHAU"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["CHICHIETKHAU"] = long.Parse(row["CHICHIETKHAU"].ToString() == "" ? "0" : row["CHICHIETKHAU"].ToString());
                        }

                        if (IsFloatNumeric(row["TONGTRICHTHUONG"].ToString()))
                        {
                            double num = double.Parse(row["TONGTRICHTHUONG"].ToString());
                            num = Math.Round(num);
                            row["TONGTRICHTHUONG"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["TONGTRICHTHUONG"] = long.Parse(row["TONGTRICHTHUONG"].ToString() == "" ? "0" : row["TONGTRICHTHUONG"].ToString());
                        }

                        if (IsFloatNumeric(row["CHITRICHTHUONG"].ToString()))
                        {
                            double num = double.Parse(row["CHITRICHTHUONG"].ToString());
                            num = Math.Round(num);
                            row["CHITRICHTHUONG"] = long.Parse(num.ToString());
                        }
                        else
                        {
                            row["CHITRICHTHUONG"] = long.Parse(row["CHITRICHTHUONG"].ToString() == "" ? "0" : row["CHITRICHTHUONG"].ToString());
                        }
                        KhachHangDTO khmoi = new KhachHangDTO
                        {
                            TENKH = row["TENKH"].ToString(),
                            DIACHI = row["DIACHI"].ToString(),
                            MADONVI = maDonVi
                        };
                        //khong match thi them vao bang khach hang
                        KhachHangDTO khcu = new KhachHangDTO();

                        if (CheckingTheSameAs(khmoi, ref khcu))
                        {
                            DataRow row2;
                            dataTable.Rows.Add();
                            row2 = dataTable.Rows[count];

                            row2["TENKHMOI"] = khmoi.TENKH;
                            row2["DIACHIMOI"] = khmoi.DIACHI;
                            row2["DONVIMOI"] = khmoi.MADONVI;

                            row2["TENKHCU"] = khcu.TENKH;
                            row2["DIACHICU"] = khcu.DIACHI;
                            row2["DONVICU"] = khcu.MADONVI;

                            row2["TONGTRICHTHUONG"] = long.Parse(row["TONGTRICHTHUONG"].ToString() == "" ? "0" : row["TONGTRICHTHUONG"].ToString());
                            row2["TONGCHIETKHAU"] = long.Parse(row["TONGCHIETKHAU"].ToString());
                            row2["CHITRICHTHUONG"] = long.Parse(row["CHITRICHTHUONG"].ToString() == "" ? "0" : row["CHITRICHTHUONG"].ToString());
                            row2["CHICHIETKHAU"] = long.Parse(row["CHICHIETKHAU"].ToString());

                            row2["BPTN"] = long.Parse(row["BPTN"].ToString() == "" ? "0" : row["BPTN"].ToString());
                            row2["BKTN"] = long.Parse(row["BKTN"].ToString() == "" ? "0" : row["BKTN"].ToString());
                            row2["EMSTN"] = long.Parse(row["EMSTN"].ToString() == "" ? "0" : row["EMSTN"].ToString());
                            row2["EMSNN"] = long.Parse(row["EMSNN"].ToString() == "" ? "0" : row["EMSNN"].ToString());
                            row2["VE"] = long.Parse(row["VE"].ToString() == "" ? "0" : row["VE"].ToString());
                            row2["BCUT"] = long.Parse(row["BCUT"].ToString() == "" ? "0" : row["BCUT"].ToString());
                            row2["TG"] = long.Parse(row["TG"].ToString() == "" ? "0" : row["TG"].ToString());
                            row2["TM"] = long.Parse(row["TM"].ToString() == "" ? "0" : row["TM"].ToString());
                            row2["TBT"] = long.Parse(row["TBT"].ToString() == "" ? "0" : row["TBT"].ToString());

                            count++;
                        }
                    }

                    DataView objTimeSheetDV = new System.Data.DataView(dataTable);
                    objTimeSheetDV.AllowNew = false;
                    dtgvDanhSachHienThi.AutoGenerateColumns = false;
                    dtgvDanhSachHienThi.DataSource = objTimeSheetDV;

                    DataGridViewTextBoxColumn objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH mới",
                        DataPropertyName = "TenKHMoi",
                        Name = "TenKHMoi",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Đơn vị mới",
                        DataPropertyName = "DonViMoi",
                        Name = "DonViMoi",
                        Width = 60
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);


                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ mới",
                        DataPropertyName = "DiaChiMoi",
                        Name = "DiaChiMoi",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH cũ",
                        DataPropertyName = "TenKHCu",
                        Name = "TenKHCu",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Đơn vị cũ",
                        DataPropertyName = "DonViCu",
                        Name = "DonViCu",
                        Width = 60
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ cũ",
                        DataPropertyName = "DiaChiCu",
                        Name = "DiaChiCu",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TongTrichThuong",
                        Name = "TongTrichThuong",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TongChietKhau",
                        Name = "TongChietKhau",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "ChiTrichThuong",
                        Name = "ChiTrichThuong",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "ChiChietKhau",
                        Name = "ChiChietKhau",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    //////////////////////////////////////////////////
                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BPTN",
                        Name = "BPTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BKTN",
                        Name = "BKTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSTN",
                        Name = "EMSTN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "EMSNN",
                        Name = "EMSNN",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "VE",
                        Name = "VE",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "BCUT",
                        Name = "BCUT",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TG",
                        Name = "TG",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TM",
                        Name = "TM",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "TBT",
                        Name = "TBT",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);
                    //////////////////////////////////////////////////
                    DataGridViewButtonColumn objColumnButton = new DataGridViewButtonColumn
                    {
                        HeaderText = "Thêm",
                        Text = "Thêm",
                        DataPropertyName = "Them",
                        Width = 80,
                        Name = "btnThem",
                        UseColumnTextForButtonValue = true,
                        ToolTipText = "Thêm thông tin vào bảng khách hàng và các bảng liên quan"
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumnButton);

                    objColumnButton = new DataGridViewButtonColumn
                    {
                        HeaderText = "Cập nhật",
                        Text = "Cập nhật",
                        DataPropertyName = "CapNhat",
                        Width = 80,
                        Name = "btnCapNhat",
                        UseColumnTextForButtonValue = true,
                        ToolTipText = "Cập nhật thông tin của khách hàng và các thông tin liên quan"
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumnButton);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        private void btnChonFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog frm = new OpenFileDialog();

            frm.Title = "Lua chon file import";
            frm.Filter = "Executable file (*.xls)|*.xls";
            frm.RestoreDirectory = true;
            frm.InitialDirectory = Application.StartupPath + "\\Imports";

            if (frm.ShowDialog() == DialogResult.OK)
            {
                string errorText = "";
                var ASynInvoke = new TimeTask(WaitLoad);
                IAsyncResult result = ASynInvoke.BeginInvoke(null, null);

                GlobalVariables.g_strPathKHLon = frm.FileName;
                lblTenDuongDan.Text = frm.FileName;
                //import du lieu khach hang lon
                maDonVi = ((DonViDTO)cbxDonVi.SelectedItem).MADONVI;
                var isvalid = ImportKhachHangLon(GlobalVariables.g_strPathKHLon, ref errorText);

                if (!isvalid)
                {
                    waiting.Hide();
                    MessageBox.Show(errorText, "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    waiting.Hide();
                    MessageBox.Show("Khách hàng và các thông tin liên quan đã được thêm", "Thông báo",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ReImportKhachHangLon(GlobalVariables.g_strPathKHLon, ref errorText);
                }

                waiting.Close();
                waiting.Dispose();
                waiting = null;

                ASynInvoke.EndInvoke(result);
            }
        }

        public void AddKhachHangAndConcerns(DataGridViewCellEventArgs e)
        {
            try
            {
                string errorText = "";
                var objBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);

                KhachHangDTO objDto = new KhachHangDTO
                {
                    MADONVI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DonViMoi"].Value.ToString(),
                    TENKH = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TenKHMoi"].Value.ToString(),
                    DIACHI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DiaChiMoi"].Value.ToString()
                };

                KhachHangDTO objCuDto = new KhachHangDTO
                {
                    MADONVI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DonViCu"].Value.ToString(),
                    TENKH = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TenKHCu"].Value.ToString(),
                    DIACHI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DiaChiCu"].Value.ToString()
                };

                if (CheckingMatching(objDto, objCuDto))
                {
                    var result = MessageBox.Show("2 khách hàng này có các thuộc tính tương đồng với nhau, bạn có chắc chắn muốn thêm không", "Cảnh báo",
                            MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (result == System.Windows.Forms.DialogResult.OK)
                    {
                        TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                        {
                            THANG = thang,
                            NAM = nam,
                            TONGTRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongTrichThuong"].Value.ToString()),
                            TONGCHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongChietKhau"].Value.ToString()),
                            CHITRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiTrichThuong"].Value.ToString()),
                            CHICHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiChietKhau"].Value.ToString())
                        };
                        if (!objBUS.InsertAndConcerns(objDto, objttckDto))
                        {
                            MessageBox.Show("Thao tác thêm thất bại", "Bao Loi",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        }
                        else
                        {
                            MessageBox.Show("Khách hàng và các thông tin liên quan (trích thưởng, chiết khấu, tổng hợp, chi tiết) đã được thêm", "Thông báo",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                            LoadAllOfKhachHang();
                            ReImportKhachHangLon(GlobalVariables.g_strPathKHLon, ref errorText);
                        }
                    }
                }
                else
                {
                    TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                    {
                        THANG = thang,
                        NAM = nam,
                        TONGTRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongTrichThuong"].Value.ToString()),
                        TONGCHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongChietKhau"].Value.ToString()),
                        CHITRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiTrichThuong"].Value.ToString()),
                        CHICHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiChietKhau"].Value.ToString())
                    };
                    if (!objBUS.InsertAndConcerns(objDto, objttckDto))
                    {
                        MessageBox.Show("Thao tác thêm thất bại", "Bao Loi",
                            MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }
                    else
                    {
                        ContinueAddTongHopChiTiet(e, objDto.MAKH);
                        MessageBox.Show("Khách hàng và các thông tin liên quan đã được thêm", "Thông báo",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                        LoadAllOfKhachHang();
                        ReImportKhachHangLon(GlobalVariables.g_strPathKHLon, ref errorText);
                    }
                }

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        public void ContinueAddTongHopChiTiet(DataGridViewCellEventArgs e, int makh)
        {
            //tiep tuc thu hien them thong tin vao bang tong hop va chi tiet
            //kiem tra khac hang o thang x nam y da ton tai trong bang tong hop hay chua
            //neu chua, thuc hien them record moi
            //da ton tai, remove record co san va them record moi
            IDictionary<string, string> tonghop_dichvu = new Dictionary<string, string>();

            tonghop_dichvu.Add("BPTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BPTN"].Value.ToString());    
            tonghop_dichvu.Add("BKTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BKTN"].Value.ToString());
            tonghop_dichvu.Add("EMSTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["EMSTN"].Value.ToString());
            tonghop_dichvu.Add("EMSNN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["EMSNN"].Value.ToString());
            tonghop_dichvu.Add("VE", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["VE"].Value.ToString());
            tonghop_dichvu.Add("BCUT", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BCUT"].Value.ToString());
            tonghop_dichvu.Add("TG", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TG"].Value.ToString());
            tonghop_dichvu.Add("TM", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TM"].Value.ToString());
            tonghop_dichvu.Add("TBT", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TBT"].Value.ToString());

            if (tonghop_dichvu.Count > 0)
            {
                var tonghop = Utils.CheckingItemIsExist(objKhachHangDS.Tables[0], makh, thang, nam);
                if (tonghop == null)
                {
                    TongHopDTO objTHDto = new TongHopDTO
                    {
                        MAKH = makh,
                        THANG = thang,
                        NAM = nam
                    };

                    //insert new record vao bang tong hop
                    var objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                    if (objTHBUS.Insert(objTHDto))
                    {
                        //if exist tonghop, just remove
                        var objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                        objCTBUS.DeleteByMATONGHOP(objTHDto.MATONGHOP);
                        for (int i = 0; i < tonghop_dichvu.Count; i++)
                        {
                            var ctDto = new ChiTietDTO
                            {
                                MADICHVU = tonghop_dichvu.Keys.ToList()[i],
                                MATONGHOP = objTHDto.MATONGHOP,
                                SOTIEN = long.Parse(tonghop_dichvu.Values.ToList()[i])
                            };
                            objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                            objCTBUS.Insert(ctDto);
                        }
                    }
                }
                else
                {
                    //if exist tonghop, just remove
                    var objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                    objCTBUS.DeleteByMATONGHOP(int.Parse(tonghop["MATONGHOP"].ToString()));
                    var objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                    objTHBUS.Delete(int.Parse(tonghop["MATONGHOP"].ToString()));

                    TongHopDTO objTHDto = new TongHopDTO
                    {
                        MAKH = makh,
                        THANG = thang,
                        NAM = nam
                    };

                    //insert new record vao bang tong hop
                    if (objTHBUS.Insert(objTHDto))
                    {
                        for (int i = 0; i < tonghop_dichvu.Count; i++)
                        {
                            var ctDto = new ChiTietDTO
                            {
                                MADICHVU = tonghop_dichvu.Keys.ToList()[i],
                                MATONGHOP = objTHDto.MATONGHOP,
                                SOTIEN = long.Parse(tonghop_dichvu.Values.ToList()[i])
                            };
                            objCTBUS.Insert(ctDto);
                        }
                    }
                }
            }
        }
        public void UpdateKhachHangAndConcerns(DataGridViewCellEventArgs e)
        {
            try
            {
                string errorText = "";
                var objBUS = new KhachHangBUS(GlobalVariables.g_strConnectionString);

                KhachHangDTO objDto = new KhachHangDTO
                {
                    MADONVI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DonViMoi"].Value.ToString(),
                    TENKH = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TenKHMoi"].Value.ToString(),
                    DIACHI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DiaChiMoi"].Value.ToString()
                };

                KhachHangDTO objCuDto = new KhachHangDTO
                {
                    MADONVI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DonViCu"].Value.ToString(),
                    TENKH = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TenKHCu"].Value.ToString(),
                    DIACHI = dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["DiaChiCu"].Value.ToString()
                };

                if (CheckingMatching(objDto, objCuDto))
                {
                    objCuDto.MAKH = int.Parse(objBUS.SelectMAKH(objCuDto.MADONVI, objCuDto.TENKH, objCuDto.DIACHI).Tables[0].Rows[0][0].ToString());
                    var result = MessageBox.Show("Mọi thông tin liên quan đến khách hàng (trích thưởng, chiết khấu, tổng hợp, chi tiết) sẽ được cập nhật, bạn có chắc chắn muốn cập nhật không?", "Cảnh báo",
                            MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (result == System.Windows.Forms.DialogResult.OK)
                    {
                        TrichThuong_ChietKhauDTO objttckDto = new TrichThuong_ChietKhauDTO
                        {
                            MAKH = objCuDto.MAKH,
                            THANG = thang,
                            NAM = nam,
                            TONGTRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongTrichThuong"].Value.ToString()),
                            TONGCHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TongChietKhau"].Value.ToString()),
                            CHITRICHTHUONG = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiTrichThuong"].Value.ToString()),
                            CHICHIETKHAU = long.Parse(dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["ChiChietKhau"].Value.ToString())
                        };

                        objCuDto.TENKH = objDto.TENKH;
                        objCuDto.DIACHI = objDto.DIACHI;

                        if (!objBUS.UpdateAndConcerns(objCuDto, objttckDto))
                        {
                            MessageBox.Show("Thao tác thêm thất bại", "Bao Loi",
                                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        }
                        else
                        {
                            IDictionary<string, string> tonghop_dichvu = new Dictionary<string, string>();

                            tonghop_dichvu.Add("BPTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BPTN"].Value.ToString());
                            tonghop_dichvu.Add("BKTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BKTN"].Value.ToString());
                            tonghop_dichvu.Add("EMSTN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["EMSTN"].Value.ToString());
                            tonghop_dichvu.Add("EMSNN", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["EMSNN"].Value.ToString());
                            tonghop_dichvu.Add("VE", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["VE"].Value.ToString());
                            tonghop_dichvu.Add("BCUT", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["BCUT"].Value.ToString());
                            tonghop_dichvu.Add("TG", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TG"].Value.ToString());
                            tonghop_dichvu.Add("TM", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TM"].Value.ToString());
                            tonghop_dichvu.Add("TBT", dtgvDanhSachHienThi.Rows[e.RowIndex].Cells["TBT"].Value.ToString());

                            //if exist tonghop, just remove
                            var objTHBUS = new TongHopBUS(GlobalVariables.g_strConnectionString);
                            objKhachHangDS = objTHBUS.SelectByMAKHTHANGNAM(objCuDto.MAKH, thang, nam);
                            for (int i = 0; i < tonghop_dichvu.Count; i++)
                            {
                                var ctDto = new ChiTietDTO
                                {
                                    MADICHVU = tonghop_dichvu.Keys.ToList()[i],
                                    MATONGHOP = int.Parse(objKhachHangDS.Tables[0].Rows[0][0].ToString()),
                                    SOTIEN = long.Parse(tonghop_dichvu.Values.ToList()[i])
                                };
                                var objCTBUS = new ChiTietBUS(GlobalVariables.g_strConnectionString);
                                objCTBUS.Update(ctDto);
                            }

                            MessageBox.Show("Khách hàng và các thông tin liên quan đã được cập nhật", "Thông báo",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                            LoadAllOfKhachHang();
                            ReImportKhachHangLon(GlobalVariables.g_strPathKHLon, ref errorText);
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Khách hàng không tồn tại trong CSDL, không thể thực hiện cập nhật", "Bao Loi",
                            MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        private void dtgvDanhSachHienThi_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == 19)
                {
                    AddKhachHangAndConcerns(e);
                }
                else if (e.ColumnIndex == 20)
                {
                    UpdateKhachHangAndConcerns(e);
                }
            }
            catch
            {
            }
        }

    }
}
